package com.dy.yunying.biz.dao.clickhouse3399.impl;

import com.dy.yunying.api.dto.HourDataDto;
import com.dy.yunying.api.vo.HourDataVo;
import com.dy.yunying.biz.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @ClassName HourDataDao
 * @Description todo
 * @Author nieml
 * @Time 2021/6/24 14:51
 * @Version 1.0
 **/
@Component
@Slf4j
public class HourDataDao {

	@Resource(name = "clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;

	public List<HourDataVo> getHourDataDim(HourDataDto req) {
		StringBuilder sql = getSqlAndName(req);
		log.info("分时数据查询sql:[{}]", sql.toString());
		List<HourDataVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<HourDataVo>(HourDataVo.class));
		return list;
	}

	public List<HourDataVo> getHourDataAll(HourDataDto req) {
		StringBuilder allSql = getAllSql(req);
		log.info("分时数据汇总查询sql:[{}]", allSql.toString());
		List<HourDataVo> allList = clickhouseTemplate.query(allSql.toString(), new Object[]{}, new BeanPropertyRowMapper<HourDataVo>(HourDataVo.class));
		return allList;
	}

	//组装分时数据汇总的查询sql todo
	private StringBuilder getAllSql(HourDataDto req) {
		StringBuilder dimSql = getHourDimSql(req);
		//查询汇总的时候，类别设置为空
		StringBuilder kpiSql = getKpiSql(req, "");
		StringBuilder query_sql = new StringBuilder();
		// 等于4 走汇总
		if (req.getCycleType() == 4) {
			query_sql.append(" select  sum(ifnull(kpi.newRegNums,0)) newRegNums,  sum(kpi.newRegPayAmount) newRegPayAmount  from ");
		} else {
			query_sql.append(" select dim.hour,ifnull(kpi.newRegNums,0) newRegNums,kpi.newRegPayAmount newRegPayAmount from ");
		}
		query_sql.append(dimSql);
		query_sql.append(" dim ");
		query_sql.append(" left join ");
		query_sql.append(kpiSql);
		query_sql.append(" on dim.hour = kpi.hour ");
		return query_sql;
	}

	//分时数据的查询sql 获取名称
	private StringBuilder getSqlAndName(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		StringBuilder sql = getSql(req);
		StringBuilder parentChlNameSql = getParentChlNameSql(req);
		if (StringUtils.isBlank(queryColumn)) {
			return sql;
		}
		StringBuilder nameSql = new StringBuilder();
		if (queryColumn.contains("parentchl")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				nameSql.append(" select  res.parentchl parentchl,  SUM(res.newRegNums) newRegNums,  sum(res.newRegPayAmount) newRegPayAmount, ");
			} else {
				nameSql.append(" select res.*, ");
			}
			nameSql.append(" case when a.parentchlName is null or a.parentchlName = '' then '未知' else a.parentchlName end parentchlName ");
			nameSql.append(" from ");
			nameSql.append("(" + sql + " ) res ");
			nameSql.append(" left join ");
			nameSql.append(parentChlNameSql);
			nameSql.append(" on res.parentchl = a.parentchl ");
			if (req.getCycleType() == 4) {
				nameSql.append(" group  by parentchl,parentchlName ");
			}
			return nameSql;
		}
		if (queryColumn.contains("deptId")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				nameSql.append(" select  res.deptId deptId,  SUM(res.newRegNums) newRegNums,  sum(res.newRegPayAmount) newRegPayAmount, ");
			} else {
				nameSql.append(" select res.*, ");
			}
			nameSql.append(" case when osd.name is null or osd.name = '' then '未知' else osd.name end deptName ");
			nameSql.append(" from ");
			nameSql.append("(" + sql + " ) res ");
			nameSql.append(" LEFT JOIN odsmysql_sys_dept osd ON res.deptId = osd.dept_id ");
			if (req.getCycleType() == 4) {
				nameSql.append(" group  by deptId,deptName ");
			}
			return nameSql;
		}
		if (queryColumn.contains("investor")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				nameSql.append(" select  res.investor investor,  SUM(res.newRegNums) newRegNums,  sum(res.newRegPayAmount) newRegPayAmount, ");
			} else {
				nameSql.append(" select res.*, ");
			}
			nameSql.append(" case when osu.real_name is null or osu.real_name = '' then '未知' else osu.real_name end investorName ");
			nameSql.append(" from ");
			nameSql.append("(" + sql + " ) res ");
			nameSql.append(" LEFT JOIN odsmysql_sys_user osu ON res.investor = osu.user_id ");
			if (req.getCycleType() == 4) {
				nameSql.append(" group  by investor,investorName ");
			}
			return nameSql;
		}
		if (queryColumn.contains("userGroupId")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				nameSql.append(" select  res.userGroupId userGroupId,  SUM(res.newRegNums) newRegNums,  sum(res.newRegPayAmount) newRegPayAmount, ");
			} else {
				nameSql.append(" select res.*, ");
			}
			nameSql.append(" case when osdg.name is null or osdg.name = '' then '未知' else osdg.name end userGroupName ");
			nameSql.append(" from ");
			nameSql.append("(" + sql + " ) res ");
			nameSql.append(" LEFT JOIN odsmysql_sys_dept_group osdg ON osdg.id = res.userGroupId ");
			if (req.getCycleType() == 4) {
				nameSql.append(" group  by userGroupId,userGroupName ");
			}
			return nameSql;
		}
		if (queryColumn.contains("pgid")) {
			if (req.getCycleType() == 4) {
				nameSql.append(" select   pgid,  SUM(ifnull(newRegNums,0)) newRegNums,   sum(newRegPayAmount) newRegPayAmount  ");
				nameSql.append(" from ");
				nameSql.append("(" + sql + " )  ");
				nameSql.append(" group by pgid ");
				return nameSql;
			}
		}
		if (queryColumn.contains("gameid")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				nameSql.append(" select  res.gameid gameid,  SUM(res.newRegNums) newRegNums,  sum(res.newRegPayAmount) newRegPayAmount, ");
			} else {
				nameSql.append(" select res.*, ");
			}
			nameSql.append(" case when owg.gname is null or owg.gname = '' then '未知' else owg.gname end gName ");
			nameSql.append(" from ");
			nameSql.append("(" + sql + " ) res ");
			nameSql.append(" LEFT JOIN odsmysql_wan_game owg ON owg.id = res.gameid ");
			if (req.getCycleType() == 4) {
				nameSql.append(" group  by gameid,gName ");
			}
			return nameSql;
		}
		if (queryColumn.contains("appchl")) {
			if (req.getCycleType() == 4) {
				nameSql.append(" select   appchl,  SUM(ifnull(newRegNums,0)) newRegNums,   sum(newRegPayAmount) newRegPayAmount  ");
				nameSql.append(" from ");
				nameSql.append("(" + sql + " )  ");
				nameSql.append(" group by appchl ");
				return nameSql;
			}
		}
		return sql;
	}

	//组装分时数据的查询sql
	private StringBuilder getSql(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		StringBuilder dimSql = getDimSql(req);
		StringBuilder kpiSql = getKpiSql(req, queryColumn);
		StringBuilder query_sql = new StringBuilder();
		query_sql.append(" select dim.*,ifnull(kpi.newRegNums,0) newRegNums,kpi.newRegPayAmount newRegPayAmount from ");
		query_sql.append(dimSql);
		query_sql.append(" left join ");
		query_sql.append(kpiSql);
		query_sql.append(" on dim.hour = kpi.hour ");
		if (StringUtils.isNotBlank(queryColumn)) {
			query_sql.append(" and dim." + queryColumn + " = kpi." + queryColumn);
		}
		return query_sql;
	}


	//获取查询指标sql
	private StringBuilder getKpiSql(HourDataDto req, String queryColumn) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, queryColumn);
		StringBuilder newRegPaySql = getNewRegPaySql(req, queryColumn);
		querySql.append(" ( ");
		querySql.append(" select reg.*,pay.newRegPayAmount  newRegPayAmount from ");
		querySql.append(newRegSql);
		querySql.append(" left join ");
		querySql.append(newRegPaySql);
		querySql.append(" on reg.hour = pay.hour ");
		if (StringUtils.isNotBlank(queryColumn)) {
			querySql.append(" and reg." + queryColumn + " = pay." + queryColumn);
		}
		querySql.append(" ) kpi ");
		return querySql;
	}


	// 获取维度sql
	private StringBuilder getDimSql(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		StringBuilder querySql = new StringBuilder();
		StringBuilder hourDimSql = getHourDimSql(req);
		//如果未选择类别
		if (StringUtils.isBlank(queryColumn)) {
			querySql.append(hourDimSql);
			querySql.append(" dim ");
			return querySql;
		}
		StringBuilder queryColumnDimSql = getQueryColumnDimSql(req);
		querySql.append(" ( ");
		querySql.append(" select h.hour,qc.* from  ");
		querySql.append(hourDimSql);
		querySql.append(" h ");
		querySql.append(" cross join ");
		querySql.append(queryColumnDimSql);
		querySql.append(" qc ");
		querySql.append(" ) ");
		querySql.append(" dim ");

		return querySql;
	}


	// 获取新增设备(新增设备注册账号的设备数)sql
	private StringBuilder getNewRegSql(HourDataDto req, String queryColumn) {
		Long date = req.getDate();
		StringBuilder querySql = new StringBuilder();
		StringBuilder selectSql = getSelectSql(queryColumn);
		StringBuilder groupBySql = getGroupBySql(queryColumn);
		StringBuilder authSql = getAuthSql(req);
		String whereCondition = getWhereCondition(req, "");
		querySql.append(" ( " +
				"\t\t\t\t-- 新增设备  累计注册账号的设备数\n" +
				"\t\t\t\tSELECT " +
				selectSql +
				"\t\t\t\t\tCOUNT(DISTINCT ud.dr_kid) newRegNums\n" +
				"\t\t\t\tfrom (\n" +
				"\t\t\t\t    -- 新增设备及其广告归因\n" +
				"\t\t\t\t\tSELECT a.day day," +
				"\t\t\t\t\t\t\ta.hour hour,\n" +
				" \t\t\t\t\t\ta.pgid pgid,\n" +
				"\t\t\t\t\t\ta.gameid gameid,\n" +
				"\t\t\t\t\t\ta.os os,\n" +
				"\t\t\t\t\t\ta.parentchl parentchl,\n" +
				"\t\t\t\t\t\ta.chl chl,\n" +
				"\t\t\t\t\t\ta.appchl appchl,\n" +
				"\t\t\t\t\t\tifnull(wpc.manage,0) investor,\n" +
				"\t\t\t\t\t\tosu.dept_id deptId,\n" +
				"\t\t\t\t\t\tIFNULL(osu.dept_group_id,0) userGroupId, \n" +
				"\t\t\t\t\t\tIFNULL(osdg.name,'') userGroupName, \n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adid is not null THEN ad.adid\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adid,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adname is not null THEN ad.adname\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adidName,\n" +
				"\t\t\t\t\t\t(case\n" +
				"\t\t\t\t\t\t\twhen ad.adaccount is not null THEN ad.adaccount\n" +
				"\t\t\t\t\t\t\telse ''\n" +
				"\t\t\t\t\tend) as adAccount, " +
				"\t\t\t\t\t\ta.uuid uuid,a.kid kid,a.receivetime receivetime,ad.advert_id advert_id\n" +
				"\t\t\t\t\tfrom thirty_game_device_reg a\n" +
				"\t\t\t\t\tLEFT JOIN v_thirty_ad_device ad\n" +
				"\t\t\t\t\ton a.uuid = ad.uuid\n" +
				"\t\t\t\t\tand a.pgid = ad.pgid\n" +
				"\t\t\t\t\tand a.day = ad.`day`\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
				"\t\t\t\t\t\twpc.isdelete = 0\n" +
				"\t\t\t\t\t\tAND wpc.parent_code = a.parentchl\n" +
				"\t\t\t\t\t\tAND wpc.chncode = a.chl\n" +
				"\t\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
				"\t\t\t\t\t\twpc.manage = osu.user_id\n" +
				"\t\t\t\t\t left join odsmysql_sys_dept_group osdg\n" +
				"\t\t\t\t\t\t on osu.dept_group_id = osdg.id\n" +
				"\t\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
				"\t\t\t\t\t\tad.adid = vad.adid\n" +
				"\t\t\t\t\t\tAND ad.ctype = vad.ctype\n" +
				"\t\t\t\t\twhere 1=1\n" +
				"\t\t\t\t\tand a.day = " +
				date +
				"\t\t\t\t) reg\n" +
				"\t\t\t\tLEFT JOIN (\n" +
				"\t\t\t\t\t-- 新增设备上注册的账号\n" +
				"\t\t\t\t\tselect\n" +
				"\t\t\t\t\t\tur.kid ur_kid,\n" +
				"\t\t\t\t\t\targMax(dr.kid, dr.receivetime) dr_kid,\n" +
				"\t\t\t--\t\t\targMax(ur.day, dr.receivetime) day,\n" +
				"\t\t\t--\t\t\targMax(ur.uuid, dr.receivetime) uuid,\n" +
				"\t\t\t\t\t\targMax(ur.usrname, dr.receivetime) usrname\n" +
				"\t\t\t--\t\t\targMax(ur.gameid, dr.receivetime) gameid,\n" +
				"\t\t\t--\t\t\targMax(dr.parentchl, dr.receivetime) parentchl,\n" +
				"\t\t\t--\t\t\targMax(dr.chl, dr.receivetime) chl,\n" +
				"\t\t\t--\t\t\targMax(dr.appchl, dr.receivetime) appchl\n" +
				"\t\t\t\t\tfrom v_game_account_reg ur\n" +
				"\t\t\t\t\tINNER join odsmysql_wan_game g\n" +
				"\t\t\t\t\ton ur.gameid = g.id\n" +
				"\t\t\t\t\tINNER join thirty_game_device_reg  dr\n" +
				"\t\t\t\t\ton ur.uuid = dr.uuid\n" +
				"\t\t\t\t\tand g.pgid = dr.pgid\n" +
				"\t\t\t\t\t--and ur.day =dr.`day`\n" +
				"\t\t\t\t\twhere 1=1\n" +
				"\t\t\t\t\tand  ur.receivetimes >= dr.receivetime\n" +
				"\t\t\t\t\tand dr.day = " +
				date +
				"\t\t\t\t--and dr.day = ur.day\n" +
				"\t\t\t\t\tgroup by ur.kid\n" +
				"\t\t\t\t) ud\n" +
				"\t\t\t\ton reg.kid = ud.dr_kid\n" +
				"\t\t\t\twhere 1=1\n" +
				"-- 筛选条件\n" +
				whereCondition +
				"-- 渠道权限\n" +
				authSql +
				"\t\t\t\tGROUP by " +
				groupBySql +
				" having newRegNums is not null " +
				" ) reg "
		);
		return querySql;
	}


	// 获取新增充值sql
	private StringBuilder getNewRegPaySql(HourDataDto req, String queryColumn) {
		Long date = req.getDate();
		StringBuilder querySql = new StringBuilder();
		StringBuilder selectSql = getSelectSql(queryColumn);
		StringBuilder groupBySql = getGroupBySql(queryColumn);
		querySql.append(" (\n" +
						" -- 新增设备充值金额：当前时段注册在当天充值的金额\n" +
						"\tselect \n" +
						selectSql +
						"\t\tCOUNT(DISTINCT z.regkid) paydeviceAll, -- 新增设备累计付费设备数\n" +
						"\t\t SUM(worth1) newRegPayAmount\n" +
						"\tfrom (\n" +
						"\t -- 新增设备充值\n" +
						"\t\tselect\n" +
						"\t\t\targMax(reg.day, reg.receivetime) regDay,\n" +
						"\t\t\targMax(reg.hour, reg.receivetime) regHour,\n" +
						"\t\t\tregDay AS day,\n" +
						"\t\t\tregHour AS hour,\n" +
						"\t\t\t\t\t\targMax(reg.pgid,\n" +
						"\t\t\t\t\t\treg.receivetime) pgid,\n" +
						"\t\t\t\t\t\targMax(reg.gameid,\n" +
						"\t\t\t\t\t\treg.receivetime) AS gameid,\n" +
						"\t\t\t\t\t\targMax(reg.os,\n" +
						"\t\t\t\t\t\treg.receivetime) AS os,\n" +
						"\t\t\t\t\t\targMax(reg.parentchl,\n" +
						"\t\t\t\t\t\treg.receivetime) parentchl,\n" +
						"\t\t\t\t\t\targMax(reg.chl,\n" +
						"\t\t\t\t\t\treg.receivetime) chl,\n" +
						"\t\t\t\t\t\targMax(reg.appchl,\n" +
						"\t\t\t\t\t\treg.receivetime) appchl,\n" +
						"\t\t\t\t\t\tifnull(argMax(wpc.manage,\n" +
						"\t\t\t\t\t\treg.receivetime),0) investor,\n" +
						"\t\t\t\t\t\targMax(osu.dept_id,\n" +
						"\t\t\t\t\t\treg.receivetime) deptId,\n" +
						"\t\t\t\t\t\tIFNULL(argMax(osu.dept_group_id,reg.receivetime),0) userGroupId, \n" +
						"\t\t\t\t\t\tIFNULL(argMax(osdg.name,reg.receivetime),'') userGroupName, \n" +
						"\t\t\t\t\t\targMax(ad.adid,\n" +
						"\t\t\t\t\t\treg.receivetime) adidTmp,\n" +
						"\t\t\t\t\t\targMax(ad.adname,\n" +
						"\t\t\t\t\t\treg.receivetime) adidName,\n" +
						"\t\t\t\t\t\targMax(ad.adaccount,\n" +
						"\t\t\t\t\t\treg.receivetime) adAccount,\n" +
						"\t\t\t\t\t\t(case\n" +
						"\t\t\t\t\t\t\twhen adidTmp is not null THEN adidTmp\n" +
						"\t\t\t\t\t\t\telse ''\n" +
						"\t\t\t\t\tend) as adid, " +
						"\t\t\trc.kid rckid,\n" +
						"\t\t\targMax(reg.kid, reg.receivetime) regkid,\n" +
						"\t\t\targMax(rc.day, reg.receivetime) payday,\n" +
						"\t\t\tdateDiff('day', parseDateTimeBestEffort(toString(day)),  today() ) as  cur_diff, --距离今天多少天\n" +
						"\t\t\tdateDiff('day', parseDateTimeBestEffort(toString(day)), parseDateTimeBestEffort(toString(payday))) as pay_diff,\n" +
						"\t\t\targMax(rc.uuid, reg.receivetime) AS payuuid,\n" +
						"\t\t\targMax(rc.fee, reg.receivetime) fee,\n" +
						"\t\t\targMax(rc.givemoney, reg.receivetime) givemoney,\n" +
						"\t\t\targMax(rc.sharfee, reg.receivetime) sharfee,\n" +
						"\t\t\t(case when pay_diff = 0 then sharfee else 0  end) worth1,\n" +
						"\t\t\targMax(rc.createtime, reg.receivetime) createtime\n" +

						"\t\tfrom v_original_user_recharge_share rc\n" +
//				"\t\tleft join odsmysql_wan_game g\n" +
//						"\t\ton rc.gameid=g.id\n" +
						"\t\tLEFT join thirty_game_device_reg reg\n" +
						"\t\ton rc.uuid=reg.uuid\n" +
						"\t\tand rc.pgid = reg.pgid\n" +
						"\t\tLEFT JOIN v_thirty_ad_device ad\n" +
						"\t\ton reg.uuid = ad.uuid\n" +
						"\t\tand reg.day = ad.day\n" +
						"\t\tand reg.pgid = ad.pgid\n" +
						"\t\tLEFT JOIN odsmysql_wan_promotion_channel_v3 wpc ON\n" +
						"\t\t\t\t\t\twpc.isdelete = 0\n" +
						"\t\t\t\t\t\tAND wpc.parent_code = reg.parentchl\n" +
						"\t\t\t\t\t\tAND wpc.chncode = reg.chl\n" +
						"\t\t\t\t\tLEFT JOIN odsmysql_sys_user osu ON\n" +
						"\t\t\t\t\t\twpc.manage = osu.user_id\n" +
						"\t\t\t\t\t left join odsmysql_sys_dept_group osdg\n" +
						"\t\t\t\t\t\t on osu.dept_group_id = osdg.id\n" +
						"\t\t\t\t\tLEFT JOIN v_odsmysql_adid vad ON\n" +
						"\t\t\t\t\t\tad.adid = vad.adid\n" +
						"\t\t\t\t\t\tAND ad.ctype = vad.ctype" +
						"\t\twhere 1=1 and\n" +
						"\t\treg.`day` = \n" +
						date +
						"\t\tAND rc.`day` = reg.`day`\n" +
						"\t\tgroup by rc.kid\n" +
						"\t) z\n" +
						"\tWHERE 1=1\n" +
						getWhereCondition(req, "") +
						"-- 渠道权限\n" +
						getAuthSql(req) +
						"\tgroup by \n" +
						groupBySql +
						") pay \t "
		);
		return querySql;
	}


// 获取LTV的sql：reg+pay计算得出


	// 获取GroupBySql
	private StringBuilder getGroupBySql(String queryColumn) {
		StringBuilder querySql = new StringBuilder();
		querySql.append("hour");
		if (StringUtils.isBlank(queryColumn)) {
			return querySql;
		}
		querySql.append(",");
		querySql.append(queryColumn);
		return querySql;
	}


	// 获取SelectSql
	private StringBuilder getSelectSql(String queryColumn) {
		StringBuilder querySql = new StringBuilder();
		querySql.append("hour");
		querySql.append(",");
		if (StringUtils.isBlank(queryColumn)) {
			return querySql;
		}
		querySql.append(queryColumn);
		querySql.append(",");
		return querySql;
	}


	//获取查询的维度sql
	private StringBuilder getQueryColumnDimSql(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		switch (queryColumn) {
			case "pgid":
				return getParentGameDimSql(req);
			case "gameid":
				return getGnameDimSql(req);
			case "parentchl":
				return getParentChlDimSql(req);
			case "appchl":
				return getAppChlDimSql(req);
			case "investor":
				return getInvestorDimSql(req);
			case "deptId":
				return getDeptDimSql(req);
			case "userGroupId":
				return getUserGroupDimSql(req);
			default:
				//todo
				return getParentGameDimSql(req);
		}
	}


	// 获取小时维度sql：每天的小时和当前小时
	private StringBuilder getHourDimSql(HourDataDto req) {
		String newteday = DateUtils.dateToString(new Date(), DateUtils.YYYYMMDD);// 今天
		StringBuilder dayHour = new StringBuilder();
		Long date = req.getDate();
		String dateStr = Long.toString(date);
		if (dateStr.equals(newteday)) {
			ArrayList<Integer> list = new ArrayList<Integer>();
			Integer hour = new Date().getHours();
			for (int i = 0; i <= hour; i++) {
				list.add(hour - i);
			}
			dayHour.append("  ( SELECT toUInt8(arrayJoin( " + list + ")) AS hour )  \n");
		} else {
			dayHour.append("  ( SELECT toUInt8(arrayJoin([23 , 22 , 21 , 20 , 19 , 18 , 17 , 16 , 15 , 14 , 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0])) AS hour )  \n");
		}
		return dayHour;
	}


	// 获取父游戏维度sql:统一用新增设备sql得到类别的个数
	private StringBuilder getParentGameDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select pgid from ");
		querySql.append(newRegSql);
		querySql.append(" group by pgid ) ");
		return querySql;
	}
	// 获取父游戏维度sql:统一用新增设备sql得到类别的个数
	private StringBuilder getGnameDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select gameid from ");
		querySql.append(newRegSql);
		querySql.append(" group by gameid ) ");
		return querySql;
	}

	// 获取父渠道维度sql
	private StringBuilder getParentChlDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select parentchl from ");
		querySql.append(newRegSql);
		querySql.append(" group by parentchl ) ");
		return querySql;
	}


	// 获取分包渠道维度sql
	private StringBuilder getAppChlDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select appchl from ");
		querySql.append(newRegSql);
		querySql.append(" group by appchl ) ");
		return querySql;
	}


	// 获取部门维度sql
	private StringBuilder getDeptDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select deptId from ");
		querySql.append(newRegSql);
		querySql.append(" group by deptId )   ");
		return querySql;
	}

	// 获取组别维度sql
	private StringBuilder getUserGroupDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select userGroupId from ");
		querySql.append(newRegSql);
		querySql.append(" group by userGroupId )   ");
		return querySql;
	}

	// 获取投放人维度sql
	private StringBuilder getInvestorDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select investor from ");
		querySql.append(newRegSql);
		querySql.append(" group by investor ) ");
		return querySql;
	}


	// 获取组别维度sql
	private StringBuilder getGroupDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		return querySql;
	}

	//where 条件
	//筛选条件
	public String getWhereCondition(HourDataDto req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();
		//系统
		Integer os = req.getOs();
		//主游戏
		String pgidArr = req.getPgidArr();
		//子游戏
		String gameidArr = req.getGameidArr();
		//主渠道
		String parentchlArr = req.getParentchlArr();
		//分包渠道
		String appchlArr = req.getAppchlArr();
		//部门
		String deptIdArr = req.getDeptIdArr();
		//组别
		String userGroupIdArr = req.getUserGroupIdArr();
		//投放人
		String investorArr = req.getInvestorArr();
		//日期
		if (StringUtils.isBlank(bieming)) {
			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append("       AND deptId IN (").append(deptIdArr).append(")");
			}
			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append("       AND investor IN (").append(investorArr).append(")");
			}
			if (org.apache.commons.lang3.StringUtils.isNotBlank(userGroupIdArr)) {
				sqlCondition.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and pgid  = " + pgidArr);
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and gameid  = " + gameidArr);
				}
			}
			if (Objects.nonNull(os)) {
				sqlCondition.append(" and os  = ").append(os);
			}
			return sqlCondition.toString();
		} else {
			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".deptId IN (").append(deptIdArr).append(")");
			}
			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".investor IN (").append(investorArr).append(")");
			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".pgid  = " + pgidArr);
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".gameid  = " + gameidArr);
				}
			}
			if (Objects.nonNull(os)) {

				sqlCondition.append(" and ").append(bieming).append(".os  = " + os);
			}
			return sqlCondition.toString();
		}
	}

	//权限关联表
	private StringBuilder getAuthSql(HourDataDto req) {
		StringBuilder authSql = new StringBuilder();
		// 权限关联表
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {

			// -- 渠道权限
			authSql.append(" AND ( \n");
			authSql.append(" investor IN ( -- 管理的账号 \n");
			authSql.append(req.getUserIds());
			authSql.append(" ) \n");
			authSql.append(" ) \n");
		}
		return authSql;
	}

	// 获取父渠道名称
	private StringBuilder getParentChlNameSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		querySql.append(" ( SELECT chncode parentchl,chnname parentchlName FROM odsmysql_wan_promotion_channel_v3 where pid = 0 ) a ");
		return querySql;
	}

}
